package database //只根据参数操作数据库

import (
	"database/sql"
	"errors"
	"fmt"
	"log"
	"shi_project/main/middle"
	"strconv"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

var (
	DB  *sql.DB //连接池对象
	err error
)

func init() {
	//数据库
	//用户名:密码啊@tcp(ip:端口)/数据库的名字
	dsn := "root:aA.879632437@tcp(127.0.0.1:3306)/user_system"
	// dsn := "root:aA.879632437@tcp(127.0.0.1:3306)/shi"
	//连接数据集
	DB, err = sql.Open("mysql", dsn) //open不会检验用户名和密码
	if err != nil {
		fmt.Println("数据库连接失败！！", err)
		return
	}
	err = DB.Ping() //尝试连接数据库
	if err != nil {
		return
	}
	fmt.Println("连接数据库成功~")
	DB.SetConnMaxLifetime(time.Second * 10) //连接存活最大时间
	DB.SetMaxIdleConns(200)                 //最大空闲连接数
	DB.SetMaxOpenConns(10)                  // 最大连接数
	// defer DB.Close()
}

// 分页结构体
type Pagination struct {
	Total    string `json:"total"`
	Current  string `json:"current"`
	PageSize string `json:"pageSize"`
}

type User struct {
	Id         string       `json:"id"`
	Username   string       `json:"username"`
	Password   string       `json:"password"`
	Phone      string       `json:"phone"`
	Email      string       `json:"email"`
	LoginTimes string       `json:"loginTimes"`
	LastLogin  string       `json:"lastLogin"`
	Character  []*Character `json:"character"`
}

type Character struct {
	Id         string          `json:"id"`
	Name       string          `json:"name"`
	User       []*User         `json:"users"`
	Permission []*MyPermission `json:"permission"`
}

type MyPermission struct {
	Id        string          `json:"id"`
	Name      string          `json:"name"`
	Type      string          `json:"type"`
	Parent_id string          `json:"parentId"`
	Children  []*MyPermission `json:"children"`
	Character []*Character    `json:"character"`
}

// 修改权限，可以设置权限路径的角色，清权限的子节点角色
type PermissionContext struct {
	MyPermission
	PathPermission []string `json:"pathPermission"`
	Descendants    []string `json:"descendants"`
}

func ARoles(character Character) (interface{}, error) {
	tx, err := DB.Begin()
	if err != nil {
		log.Fatal(err)
	}
	sqlStr := `
	INSERT INTO user_system.roles (name)
	VALUES (?);`
	result, err := tx.Exec(sqlStr, character.Name)
	if err != nil {
		fmt.Println("result=", result)
		tx.Rollback()
		return nil, err
	}
	num, err := result.LastInsertId()
	if err != nil {
		fmt.Println("err插入数据=", err)
		return nil, err
	}
	// 指定那些用户拥有这个角色
	if (character.User != nil) && (len(character.User) > 0) {
		var indexId string
		// 修改用户对应的角色
		for i, v := range character.User {
			if i == len(character.User)-1 {
				indexId += "(" + v.Id + ", " + fmt.Sprint(num) + ");"
			} else {
				indexId += `(` + v.Id + `, ` + fmt.Sprint(num) + `),`
			}
		}
		sqlStrAddRole := `insert into user_role ( user_id, role_id) values` + indexId
		result, err := tx.Exec(sqlStrAddRole)
		if err != nil {
			tx.Rollback()
			fmt.Println("给指定用户添加角色", err)
			return result, err
		}
	}
	// 设置这个角色有那些权限
	if (character.Permission != nil) && (len(character.Permission) > 0) {
		var indexId string
		// 修改用户对应的角色
		for i, v := range character.Permission {
			if i == len(character.Permission)-1 {
				indexId += "(" + fmt.Sprint(num) + ", " + v.Id + ");"
			} else {
				indexId += `(` + fmt.Sprint(num) + `, ` + v.Id + `),`
			}
		}
		sqlStrAddRole := `INSERT INTO user_system.role_permission (role_id, permission_id) VALUES ` + indexId
		fmt.Println("ch=", character.Permission[0])
		result, err := tx.Exec(sqlStrAddRole)
		if err != nil {
			tx.Rollback()
			fmt.Println("给角色添加权限", err)
			return result, err
		}
	}
	if err := tx.Commit(); err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	return num, nil
}

func DRoles(character Character) (interface{}, error) {
	sqlStr := "delete from roles where id = ?;"
	result, err := DB.Exec(sqlStr, character.Id)
	if err != nil {
		return nil, err
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		fmt.Println("err=", err)
		return nil, err
	}
	return rowsAffected, nil
}

func PRoles(character Character) (interface{}, error) {
	tx, err := DB.Begin()
	if err != nil {
		log.Fatal(err)
	}
	sqlStr := `
	UPDATE user_system.roles SET name = ? WHERE id = ?;
	`
	result, err := tx.Exec(sqlStr, character.Name, character.Id)
	if err != nil {
		tx.Rollback()
		return nil, err
	}
	rows, err := result.RowsAffected()
	if err != nil {
		tx.Rollback()
		return nil, err
	}
	// 清空角色的所有权限
	sqlClearPermission := `delete from role_permission where role_id = ` + character.Id + `;`
	result, err = tx.Exec(sqlClearPermission)
	if err != nil {
		tx.Rollback()
		fmt.Println("清空指定角色的所有权限", err)
		return result, err
	}
	if len(character.Permission) > 0 {
		var indexId string
		// 修改用户对应的角色
		for i, v := range character.Permission {
			if i == len(character.Permission)-1 {
				indexId += "(" + character.Id + ", " + v.Id + ");"
			} else {
				indexId += `(` + character.Id + `, ` + v.Id + `),`
			}
		}
		sqlStrAddPermission := `INSERT INTO user_system.role_permission (role_id, permission_id) VALUES` + indexId
		result, err = tx.Exec(sqlStrAddPermission)
		if err != nil {
			tx.Rollback()
			fmt.Println("给指定用户添加角色", err)
			return result, err
		}
	}
	// 清空角色下的所有用户
	sqlClearRoleFormUser := `delete from user_role where role_id = ` + character.Id + `;`
	result, err = tx.Exec(sqlClearRoleFormUser)
	if err != nil {
		tx.Rollback()
		fmt.Println("清空指定角色的所有用户", err)
		return result, err
	}
	if len(character.User) > 0 {
		var indexId string
		// 修改角色对应的用户
		for i, v := range character.User {
			if i == len(character.User)-1 {
				indexId += "(" + v.Id + ", " + character.Id + ");"
			} else {
				indexId += `(` + v.Id + `, ` + character.Id + `),`
			}
		}
		sqlStrAddUserFormCharacter := `INSERT INTO user_system.user_role (user_id, role_id) VALUES ` + indexId
		result, err = tx.Exec(sqlStrAddUserFormCharacter)
		if err != nil {
			tx.Rollback()
			fmt.Println("给指定用户添加角色", err)
			return result, err
		}
	}

	if err := tx.Commit(); err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	return rows, err
}

// 添加的时候判断有没有,有就不添加了
func appendUser(arr []*User, item *User) []*User {
	if item.Id == "" {
		return arr
	}
	isHas := false
	for _, v := range arr {
		if v.Id == item.Id {
			isHas = true
		}
	}
	if isHas {
		return arr
	} else {
		return append(arr, item)
	}
}
func appendMyPermission(arr []*MyPermission, item *MyPermission) []*MyPermission {
	if item.Id == "" {
		return arr
	}
	isHas := false
	for _, v := range arr {
		if v.Id == item.Id {
			isHas = true
		}
	}
	if isHas {
		return arr
	} else {
		return append(arr, item)
	}
}

// 把权限与用户都处理到角色中
func handleRoleToPermissionAndUser(plat *[]Character, c Character, u User, p MyPermission) {
	isInPlat := false
	for i := range *plat {
		if (*plat)[i].Id == c.Id {
			isInPlat = true
			(*plat)[i].User = appendUser((*plat)[i].User, &u)
			(*plat)[i].Permission = appendMyPermission((*plat)[i].Permission, &p)
		}
	}
	// 盘子里面没有,加进去
	if !isInPlat {
		c.User = appendUser(c.User, &u)
		c.Permission = appendMyPermission(c.Permission, &p)
		*plat = append(*plat, c)
	}
}

// 分页获取角色列表,可按照角色名查询
func GetRolesList(c Character, pagination Pagination) ([]Character, Pagination, error) {
	tx, err := DB.Begin()
	if err != nil {
		log.Fatal(err)
	}
	var options = Pagination{}
	// 查询表的总数量
	err = tx.QueryRow("SELECT COUNT(*) FROM user_system.roles").Scan(&options.Total)
	if err != nil {
		tx.Rollback()
		return nil, options, err
	}
	numCurrent, _ := strconv.Atoi(pagination.Current)
	numPagesize, _ := strconv.Atoi(pagination.PageSize)
	offset := (numCurrent - 1) * numPagesize
	sqlPagination := `limit ` + pagination.PageSize + ` offset ` + fmt.Sprint(offset)
	var sqlSearchName string
	if c.Name != "" {
		sqlSearchName = `where r.name like '%` + c.Name + `%'`
	}
	sqlStr := `
	select
		r.id,
		r.name,
		IFNULL(u.id, '')   as userId,
		IFNULL(u.username, '') as username,
		IFNULL(u.email, '') as email,
		IFNULL(u.phone, '') as phone,
		IFNULL(u.login_times, '') as loginTimes,
		IFNULL(u.last_login, '') as lastLogin,
		IFNULL(p.id, '')   as permissionId,
		IFNULL(p.name, '') as permissionName,
		IFNULL(p.type, '') as type,
		IFNULL(p.parent_id, '') as parentId
	from roles r
		left join user_system.user_role ur on r.id = ur.role_id
		left join user_system.user u on u.id = ur.user_id
		LEFT JOIN user_system.role_permission rp ON r.id = rp.role_id
		LEFT JOIN user_system.permission p ON rp.permission_id = p.id
` + sqlSearchName + `order by id desc ` + sqlPagination + `;`
	rows, err := tx.Query(sqlStr)
	if err != nil {
		tx.Rollback()
		return nil, options, err
	}
	defer rows.Close()
	// treePermission, err := GPermissionList(MyPermission{})
	var list []Character
	for rows.Next() {
		c := Character{}
		u := User{}
		p := MyPermission{}
		err = rows.Scan(&c.Id, &c.Name,
			&u.Id, &u.Username, &u.Email, &u.Phone, &u.LoginTimes, &u.LastLogin,
			&p.Id, &p.Name, &p.Type, &p.Parent_id)
		if err != nil {
			tx.Rollback()
			log.Fatal(err)
		}
		handleRoleToPermissionAndUser(&list, c, u, p)
	}

	if err := tx.Commit(); err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	return list, options, nil
}

// 删除用户
func DUser(u User) (int64, error) {
	sqlStr := "delete from user where id = ?;"
	result, err := DB.Exec(sqlStr, u.Id)
	if err != nil {
		fmt.Println("err=", err)
		return -1, err
	}
	num, err := result.RowsAffected()
	if err != nil {
		return -1, err
	}
	fmt.Println("result删除=", result)
	return num, nil
}

func VerifyLogin(u map[string]interface{}) (map[string]string, error) {
	var m = make(map[string]string)
	sqlStr := `select id, password, login_times	 from user where username = ?;`
	var id string
	var realPassword string
	var loginTimes string
	err := DB.QueryRow(sqlStr, u["username"]).Scan(&id, &realPassword, &loginTimes)
	m["id"] = id
	if err != nil {
		fmt.Println("err=", err)
		return map[string]string{"status": "没有这个用户"}, err
	}
	if realPassword == fmt.Sprint(u["password"]) {
		// 登录次数加1
		num, err := strconv.Atoi(loginTimes)
		if err != nil {
			fmt.Println("err登录次数数据转换=", err)
		}
		num++
		sqlStr := `update user set login_times = ? where id = ?;`
		_, err = DB.Exec(sqlStr, num, id)
		if err != nil {
			fmt.Println("err登录次数更新=", err)
		}
		// 生成一个最新的token
		m["token"] = middle.BuildToken(id)
		m["loginTimes"] = loginTimes
		return m, nil
	} else {
		return map[string]string{"status": "400", "msg": "密码错误"}, errors.New("密码错误")
	}
}

func AUser(u User) (interface{}, error) {
	tx, err := DB.Begin()
	if err != nil {
		log.Fatal(err)
	}
	sqlStr := `
	INSERT INTO user_system.user (username, password, email, phone)
	VALUES (?, ?, ?, ?);`
	data, err := tx.Exec(sqlStr, u.Username, u.Password, u.Email, u.Phone)
	if err != nil {
		fmt.Println("err插入数据=", err)
		tx.Rollback()
		return "", err
	}
	num, err := data.LastInsertId()
	if err != nil {
		tx.Rollback()
		return nil, err
	}
	fmt.Println("num=", num)
	var indexId string
	if (u.Character != nil) && (len(u.Character) > 0) {
		// 修改用户对应的角色
		for i, v := range u.Character {
			if i == len(u.Character)-1 {
				indexId += "(" + fmt.Sprint(num) + ", " + v.Id + ");"
			} else {
				indexId += `(` + fmt.Sprint(num) + `, ` + v.Id + `),`
			}
		}
		sqlStrAddRole := `insert into user_role ( user_id, role_id) values` + indexId
		result, err := tx.Exec(sqlStrAddRole)
		if err != nil {
			tx.Rollback()
			fmt.Println("给指定用户添加角色", err)
			return result, err
		}
	}
	if err := tx.Commit(); err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	// 关联角色
	return num, nil
}

func APermissionList(p PermissionContext) (interface{}, error) {
	tx, err := DB.Begin()
	if err != nil {
		log.Fatal(err)
	}
	if p.Parent_id == "" {
		p.Parent_id = "NULL"
	}
	sql := ` INSERT INTO user_system.permission (id, name, type, parent_id) VALUES (null, ?, ?, ` + p.Parent_id + `); `
	result, err := tx.Exec(sql, p.Name, p.Type)
	if err != nil {
		fmt.Println("err=", err)
		return nil, err
	}
	id, err := result.LastInsertId()
	if err != nil {
		return nil, err
	}
	// 给权限路径加上新建的角色
	if (p.Character != nil) && (len(p.Character) > 0) {
		var indexId string
		for _, v := range p.Character {
			indexId += `(` + v.Id + `, ` + fmt.Sprint(id) + `),`
			if p.PathPermission != nil && len(p.PathPermission) > 0 {
				// 处理路径权限
				for _, j := range p.PathPermission {
					indexId += `(` + v.Id + `, ` + j + `),`
				}
			}
		}
		indexId = strings.TrimSuffix(indexId, ",")
		sqlStrAddPermission := `INSERT IGNORE INTO user_system.role_permission (role_id, permission_id) VALUES` + indexId
		result, err = tx.Exec(sqlStrAddPermission)
		if err != nil {
			tx.Rollback()
			return result, err
		}
	}
	if err := tx.Commit(); err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	return id, nil
}

// 删除权限
func DPermission(permission MyPermission) (int64, error) {
	sqlStr := "delete from permission where id = ?;"
	result, err := DB.Exec(sqlStr, permission.Id)
	if err != nil {
		fmt.Println("err=", err)
		return -1, err
	}
	num, err := result.RowsAffected()
	if err != nil {
		return -1, err
	}
	fmt.Println("result删除=", result)
	return num, nil
}

// 删除角色权限 仅删除指定的角色权限关联表
func DRolePermission(p MyPermission) (int64, error) {
	// 收集所有子权限 id
	idListArr := collectPermissionIds(&p)
	idList := strings.Join(idListArr, ",")
	if idList == "" {
		return 0, errors.New("没有指定的权限")
	}
	sqlStr := `DELETE FROM user_system.role_permission WHERE role_id = ? AND permission_id IN (` + idList + `);`
	roleId := p.Character[0].Id
	result, err := DB.Exec(sqlStr, roleId)
	if err != nil {
		fmt.Println("err=", err)
		return -1, err
	}
	num, err := result.RowsAffected()
	if err != nil {
		return -1, err
	}
	return num, nil
}

func collectPermissionIds(node *MyPermission) []string {
	var ids []string
	ids = append(ids, node.Id)
	for _, child := range node.Children {
		ids = append(ids, collectPermissionIds(child)...)
	}
	return ids
}

func PPermissionList(p PermissionContext) (interface{}, error) {
	tx, err := DB.Begin()
	if err != nil {
		log.Fatal(err)
	}
	if p.Parent_id == "" {
		p.Parent_id = "NULL"
	}
	sqlStr := `
	UPDATE user_system.permission SET name = ?, type = ?, parent_id=` + p.Parent_id + ` WHERE id = ?;
	`
	result, err := tx.Exec(sqlStr, p.Name, p.Type, p.Id)
	if err != nil {
		tx.Rollback()
		fmt.Println("err插入数据=", err)
		return nil, err
	}
	res, err := result.RowsAffected()
	if err != nil {
		tx.Rollback()
		fmt.Println("err插入数据=", err)
		return nil, err
	}
	// 清空有这个权限的角色
	if len(p.Descendants) > 0 {
		strDescendants := strings.Join(p.Descendants, ",")
		sqlClearCharacterByPermission := `DELETE FROM role_permission WHERE permission_id IN (` + strDescendants + `);`
		result, err = tx.Exec(sqlClearCharacterByPermission)
		if err != nil {
			tx.Rollback()
			fmt.Println("清空指定角色的所有权限", err)
			return result, err
		}
	}
	// 重新给这个权限上对应的角色
	if (p.Character != nil) && (len(p.Character) > 0) {
		var indexId string
		for _, v := range p.Character {
			indexId += `(` + v.Id + `, ` + p.Id + `),`
			if p.PathPermission != nil && len(p.PathPermission) > 0 {
				// 处理路径权限
				for _, j := range p.PathPermission {
					indexId += `(` + v.Id + `, ` + j + `),`
				}
			}
		}
		indexId = strings.TrimSuffix(indexId, ",")
		sqlStrAddPermission := `INSERT IGNORE INTO user_system.role_permission (role_id, permission_id) VALUES` + indexId
		result, err = tx.Exec(sqlStrAddPermission)
		if err != nil {
			tx.Rollback()
			fmt.Println("给指定用户添加角色", err)
			return result, err
		}
	}

	if err := tx.Commit(); err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	return res, nil
}

func loop(item *MyPermission, plat *[]*MyPermission) {
	if item.Parent_id == "" {
		*plat = append(*plat, item)
		return
	}
	for _, v := range *plat {
		if v.Id == item.Parent_id {
			v.Children = append(v.Children, item)
			return
		}
		if len(v.Children) > 0 {
			loop(item, &v.Children)
		}
	}
}

// 根据权限列表转换成权限树
func buildPermissionTreeByList(list []*MyPermission, plat *[]*MyPermission) {
	// 如果长度为1,且不是根节点,直接返回这个树
	if len(list) == 1 {
		*plat = append(*plat, list[0])
		return
	}
	for _, v := range list {
		loop(v, plat)
	}
}

// 根据角色id获取角色信息
func getRoleInfoByRoleId(c Character) Character {
	res := Character{}
	tx, err := DB.Begin()
	if err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	sqlStr := `
	select id, name from roles where id = ?;`
	rows, err := tx.Query(sqlStr, c.Id)
	if err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	for rows.Next() {
		err = rows.Scan(&res.Id, &res.Name)
		if err != nil {
			tx.Rollback()
			log.Fatal(err)
		}
	}
	tx.Commit()
	return res
}

// 根据权限id获取角色列表
func getRolesListByPermission(p MyPermission) []*Character {
	var res []*Character
	tx, err := DB.Begin()
	if err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	sqlStr := `
	SELECT role_id
	FROM role_permission where permission_id = ?;`
	rows, err := tx.Query(sqlStr, p.Id)
	if err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	for rows.Next() {
		var r Character = Character{}
		err = rows.Scan(&r.Id)
		if err != nil {
			tx.Rollback()
			log.Fatal(err)
		}
		roleInfo := getRoleInfoByRoleId(r)
		res = append(res, &roleInfo)
	}
	tx.Commit()
	return res
}

// 权限管理页-权限管理-获取所有的权限列表,可根据权限名搜索
func GPermissionList(p MyPermission) ([]*MyPermission, error) {
	tx, err := DB.Begin()
	if err != nil {
		log.Fatal(err)
	}
	sqlQueryName := ``
	if p.Name != "" {
		sqlQueryName = `where name = "` + p.Name + `"`
	}
	sqlStr := `
		select
		p.id,
		p.name,
		p.type,
		ifnull(p.parent_id, '') as parent_id
		from user_system.permission p ` + sqlQueryName + `;`
	rows, err := tx.Query(sqlStr)
	if err != nil {
		return nil, err
	}
	var arr []*MyPermission
	var res []*MyPermission
	for rows.Next() {
		var p MyPermission = MyPermission{}
		err = rows.Scan(&p.Id, &p.Name, &p.Type, &p.Parent_id)
		if err != nil {
			tx.Rollback()
			fmt.Println("err=", err)
		}
		r := getRolesListByPermission(p)
		p.Character = r
		arr = append(arr, &p)
	}
	tx.Commit()
	buildPermissionTreeByList(arr, &res)
	return res, nil
}

// 根据用户id修改用户信息及角色
func PUser(u User) (interface{}, error) {
	tx, err := DB.Begin()
	if err != nil {
		log.Fatal(err)
	}
	sqlStr := `
	UPDATE user_system.user SET username = ?, password = ?, email = ?, phone = ? WHERE id = ?;
	`
	fmt.Printf("u_value:%v----u_type:%T\n", u, u)
	result, err := tx.Exec(sqlStr, u.Username, u.Password, u.Email, u.Phone, u.Id)
	if err != nil {
		tx.Rollback()
		fmt.Println("err修改sql", err)
		return result, err
	}
	// 清空用户所有的角色
	sqlClearRole := `delete from user_role where user_id = ` + u.Id + `;`
	result, err = tx.Exec(sqlClearRole)
	if err != nil {
		tx.Rollback()
		fmt.Println("清空指定用户的所有角色", err)
		return result, err
	}
	if (u.Character != nil) && (len(u.Character)) > 0 {
		var indexId string
		// 修改用户对应的角色
		for i, v := range u.Character {
			if i == len(u.Character)-1 {
				indexId += "(" + u.Id + ", " + v.Id + ");"
			} else {
				indexId += `(` + u.Id + `, ` + v.Id + `),`
			}
		}
		sqlStrAddRole := `insert into user_role ( user_id, role_id) values` + indexId
		result, err = tx.Exec(sqlStrAddRole)
		if err != nil {
			tx.Rollback()
			fmt.Println("给指定用户添加角色", err)
			return result, err
		}
	}
	if err := tx.Commit(); err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	return result, nil
}

func permissionListPutInPermission(arr *[]*MyPermission, p MyPermission) *[]*MyPermission {
	if p.Id == "" {
		return arr
	}
	var isHas = false
	for _, v := range *arr {
		if v.Id == p.Id {
			isHas = true
		}
	}
	if isHas {
	} else {
		*arr = append(*arr, &p)
	}
	return arr
}
func characterListPutInCharacterPermission(arr *[]*Character, c Character, p MyPermission) *[]*Character {
	if c.Id == "" {
		return arr
	}
	var isHas = false
	for i, v := range *arr {
		if v.Id == c.Id {
			isHas = true
			(*arr)[i].Permission = *permissionListPutInPermission(&(*arr)[i].Permission, p)
		}
	}
	if isHas {
	} else {
		c.Permission = append(c.Permission, &p)
		*arr = append(*arr, &c)
	}
	return arr
}
func userListPutInUserCharacterPermission(arr *[]*User, u User, c Character, p MyPermission) *[]*User {
	if u.Id == "" {
		return arr
	}
	var isHas = false
	for i, v := range *arr {
		if v.Id == u.Id {
			isHas = true
			(*arr)[i].Character = *characterListPutInCharacterPermission(&(*arr)[i].Character, c, p)
		}
	}
	if isHas {

	} else {
		if c.Id != "" {
			u.Character = append(u.Character, &c)
			if p.Id != "" {
				c.Permission = append(c.Permission, &p)
			}
		}
		*arr = append(*arr, &u)
	}
	return arr
}

// 根据分页 用户名 获取用户列表
func GUsers(u User, p Pagination) ([]*User, Pagination, error) {
	tx, err := DB.Begin()
	if err != nil {
		log.Fatal(err)
	}
	var options = Pagination{}
	// 查询表的总数量
	err = tx.QueryRow("SELECT COUNT(*) FROM user_system.user").Scan(&options.Total)
	if err != nil {
		tx.Rollback()
		return nil, options, err
	}
	numCurrent, _ := strconv.Atoi(p.Current)
	numPagesize, _ := strconv.Atoi(p.PageSize)
	offset := (numCurrent - 1) * numPagesize
	sqlPagination := `limit ` + p.PageSize + ` offset ` + fmt.Sprint(offset)
	var sqlSearchUsername string
	if u.Username != "" {
		sqlSearchUsername = `where username like '%` + u.Username + `%'`
	}
	// 获取用户列表
	sqlStr := `
	SELECT
		IFNULL(u.id, '')   as userId,
		IFNULL(u.username, '') as username,
		IFNULL(u.email, '') as email,
		IFNULL(u.phone, '') as phone,
		IFNULL(u.login_times, '') as loginTimes,
		IFNULL(u.last_login, '') as lastLogin,
		IFNULL(r.id, '')   as characterId,
		IFNULL(r.name, '') as characterName,
		IFNULL(p.id, '')   as permissionId,
		IFNULL(p.name, '') as permissionName,
		IFNULL(p.type, '') as permissionType,
		IFNULL(p.parent_id, '') as permissionParentId
	FROM user_system.user u
		LEFT JOIN user_system.user_role ur ON u.id = ur.user_id
		LEFT JOIN user_system.roles r ON ur.role_id = r.id
		LEFT JOIN user_system.role_permission rp ON r.id = rp.role_id
		LEFT JOIN user_system.permission p ON rp.permission_id = p.id
	` + sqlSearchUsername + sqlPagination + `;`
	rows, err := tx.Query(sqlStr)
	if err != nil {
		tx.Rollback()
		fmt.Println("err=", err)
	}

	defer rows.Close()
	// treePermission, err := GPermissionList(MyPermission{})
	if err != nil {
		tx.Rollback()
		return nil, options, err
	}
	var results []*User
	for rows.Next() {
		var u User
		var c Character
		var p MyPermission
		if err := rows.Scan(
			&u.Id, &u.Username, &u.Email, &u.Phone, &u.LoginTimes, &u.LastLogin,
			&c.Id, &c.Name,
			&p.Id, &p.Name, &p.Type, &p.Parent_id); err != nil {
			fmt.Println("err=", err)
			tx.Rollback()
			return nil, options, err
		}
		userListPutInUserCharacterPermission(&results, u, c, p)
	}
	if err := tx.Commit(); err != nil {
		tx.Rollback()
		log.Fatal(err)
	}
	return results, options, nil
}

// 获取一个人的用户信息
func GetUserInfo(id int64) ([]*User, error) {
	// 获取用户列表
	sqlStr := `
		SELECT
		IFNULL(u.id, '')   as userId,
		IFNULL(u.username, '') as username,
		IFNULL(u.email, '') as email,
		IFNULL(u.phone, '') as phone,
		IFNULL(u.login_times, '') as loginTimes,
		IFNULL(u.last_login, '') as lastLogin,
		IFNULL(r.id, -1)   as characterId,
		IFNULL(r.name, '') as characterName,
		IFNULL(p.id, -1)   as permissionId,
		IFNULL(p.name, '') as permissionName,
		IFNULL(p.type, '') as permissionType,
		IFNULL(p.parent_id, -1) as permissionParentId
	FROM user_system.user u
		LEFT JOIN user_system.user_role ur ON u.id = ur.user_id
		LEFT JOIN user_system.roles r ON ur.role_id = r.id
		LEFT JOIN user_system.role_permission rp ON r.id = rp.role_id
		LEFT JOIN user_system.permission p ON rp.permission_id = p.id
		where u.id = ?;
	`
	rows, err := DB.Query(sqlStr, id)
	if err != nil {
		fmt.Println("err=", err)
	}
	defer rows.Close()
	if err != nil {
		return nil, err
	}
	var userList []*User
	for rows.Next() {
		var u User
		var c Character
		var p MyPermission
		if err := rows.Scan(
			&u.Id, &u.Username, &u.Email, &u.Phone, &u.LoginTimes, &u.LastLogin,
			&c.Id, &c.Name,
			&p.Id, &p.Name, &p.Type, &p.Parent_id); err != nil {
			fmt.Println("err=", err)
			return nil, err
		}
		userListPutInUserCharacterPermission(&userList, u, c, p)
	}

	return userList, nil
}

// 把用户的权限树转换成数组
func GetUserPermissionsListByTree(list []*User) ([]*MyPermission, error) {
	var arr []*MyPermission
	for _, u := range list {
		for _, c := range u.Character {
			for range c.Permission {
				arr = append(arr, c.Permission...)
			}
		}
	}
	return arr, nil
}

// 获取所有用户,只获取用户的信息,不获取用户的角色及权限
func GetUserList() ([]*User, error) {
	tx, err := DB.Begin()
	if err != nil {
		log.Fatal(err)
	}
	sqlStr := `
	select u.id, u.username, IFNULL(u.email, '') as email, u.phone, u.login_times, u.last_login
	from user u;`
	rows, err := tx.Query(sqlStr)
	if err != nil {
		tx.Rollback()
		fmt.Println("err=", err)
	}
	defer rows.Close()
	userList := []*User{}
	for rows.Next() {
		var u User
		if err := rows.Scan(&u.Id, &u.Username, &u.Email, &u.Phone, &u.LoginTimes, &u.LastLogin); err != nil {
			fmt.Println("err=", err)
			tx.Rollback()
			return nil, err
		}
		userList = append(userList, &u)
	}
	return userList, nil
}
